package com.zsl.util;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DbHelper {
    private static Connection conn = null;
    private static PreparedStatement ps = null;
    private static ResultSet rs = null;

    public static Connection getConn() {
        return conn;
    }

    public static PreparedStatement getPs() {
        return ps;
    }

    public static int executeUpdate(String sql, Object[] params) {
        int res = 0;
        try {
            //conn = JdbcUtil.getConnection();
            conn = C3P0Util.getConnection();
            ps = conn.prepareStatement(sql);

            //对？赋值
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    ps.setObject(i + 1, params[i]);
                }
            }
            res = ps.executeUpdate();
            C3P0Util.release(ps, conn, rs);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return res;
    }

    public static ResultSet executeQuery(String sql, Object[] params) {
        ResultSet rs = null;
        try {
            //conn = JdbcUtil.getConnection();
            conn = C3P0Util.getConnection();
            ps = conn.prepareStatement(sql);
            //对？赋值
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    ps.setObject(i + 1, params[i]);
                }
            }
            rs = ps.executeQuery();
//			JdbcUtil.release(ps, conn, rs);	//延迟关闭
            //C3P0Util.release(ps, conn, rs);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return rs;
    }

    public static int executeDelete(String sql, Object[] params) {
        int res = 0;
        try {
            //conn = JdbcUtil.getConnection();
            conn = C3P0Util.getConnection();
            ps = conn.prepareStatement(sql);

            //对？赋值
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    ps.setObject(i + 1, params[i]);
                }
            }
            res = ps.executeUpdate();
            C3P0Util.release(ps, conn, rs);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return res;
    }

    //更自由的--更新（可以自定义参数以及where子句
    public static int update(String tableName, String[] setColumns, String[] newValues,
                             String[] whereColumns, String[] whereValues) {
        StringBuilder sql = new StringBuilder("UPDATE " + tableName + " SET " + setColumns[0] + "=?");
        int res = 0;
        //设置set ?个数
        for (int i = 1, setLen = setColumns.length; i < setLen; ++i) {
            sql.append(" ,").append(setColumns[i]).append("=?");
        }
        //设置where ?
        if (whereColumns != null) {
            sql.append(" WHERE ").append(whereColumns[0]).append("=? ");
            for (int i = 1, whereLen = whereColumns.length; i < whereLen; ++i) {
                sql.append("AND ").append(whereColumns[i]).append("=?");
            }
        }
        int index = 1;
        ResultSet rs = null;

        try {
            conn = C3P0Util.getConnection();
            ps = conn.prepareStatement(sql.toString());
            //对?赋值
            if (newValues != null) {
                for (int i = 0; i < setColumns.length; i++) {
                    ps.setObject(index, newValues[i]);
                    index++;
                }
                if (whereValues != null) {
                    for (int i = 0; i < whereColumns.length; i++) {
                        ps.setObject(index, whereValues[i]);
                        index++;
                    }
                }
            }
            System.out.println(ps);
            res = ps.executeUpdate();
            C3P0Util.release(ps, conn, rs);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return res;
    }
}
